﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Group3.MediaTool.Data.Generator;
using Group3.MediaTool.Data.IO;
using Group3.MediaTool.Core.ObjectGenerator;
using Group3.MediaTool.Provider;
using Group3.MediaTool.Interface;
using Group3.MediaTool.Core;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.IO;
using System.Reflection;
using Group3.MediaTool.TestClassenBibliothek;
using Group3.MediaTool.Generator;

namespace Group3.MediaTool.UnitTests
{
    /// <summary>
    /// Zusammenfassungsbeschreibung für SQLProviderTest
    /// </summary>
    [TestClass]
    public class SQLProviderTest
    {

        //String path = Path.Combine(StaticConfig.pathDataDefinition, "DataDefinitionWithData.xml");

        public SQLProviderTest()
        {
            //
            // TODO: Konstruktorlogik hier hinzufügen
            //
        }

        public void resetDatabase(bool testDefinition, bool createDefinition)
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;
            StaticConfig.pathDataDefinition = Path.Combine("C:\\Users\\Armari\\VS_Projekte\\g3MediaTool", "DataDefinition\\");

            if (testDefinition)
            {
                string xmlPath = Path.Combine(StaticConfig.pathDataDefinition, "DataDefinition.xml");
                string xmlPathOriginal = Path.Combine(StaticConfig.pathDataDefinition, "DataDefinition_original.xml");

                File.Copy(xmlPathOriginal, xmlPath, true);
                StaticConfig.pathDataDefinitionFile = xmlPath;
            }
            else
            {
                string xmlPath = Path.Combine(StaticConfig.pathDataDefinition, "DataDefinitionWithData.xml");
                string xmlPathOriginal = Path.Combine(StaticConfig.pathDataDefinition, "DataDefinitionWithData_original.xml");

                File.Copy(xmlPathOriginal, xmlPath, true);
                StaticConfig.pathDataDefinitionFile = xmlPath;
            }

            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            sqlConn.Connect = StaticConfig.databaseConnection;
            sqlConn.createDatabase();
            sqlConn.dropAllTabels();

            if (createDefinition) { 
                DataDefinitionClass ddBig = new DataDefinitionClass();
                ddBig.createObjects();
            }
        }

        /// <summary>
        /// This test writes an object to a databasefile and reads a list, including the written object.
        /// </summary>
        [TestMethod]
        public void SQLDatabaseWriteAndReadTest_Simple()
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            sqlConn.Connect = StaticConfig.databaseConnection;
            resetDatabase(true, true);

            //Class init
            E_TestClass2 testclass = new E_TestClass2();
            testclass.Testproperty = "Test";
            //Provider def

            Assert.IsTrue(sqlConn.SaveChanges(testclass));
            //reads the data from database
            var testdata = sqlConn.GetDataFromStore<E_TestClass2>();
            Assert.IsNotNull(testdata);
            var testobj = testdata.First();
            Assert.IsTrue(testclass.Testproperty == ((E_TestClass2)testobj).Testproperty);

        }


        /// <summary>
        /// This test writes an object to a databasefile and reads a list, including the written object.
        /// </summary>
        [TestMethod]
        public void SQLDatabaseWriteAndReadTestAttributes()
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            sqlConn.Connect = StaticConfig.databaseConnection;
            resetDatabase(false, true);

            //Class init
            E_Book2 book = new E_Book2();
            book.Title = "Titel";
            //book. = "1";
            book.Isbn11 = "111-111-111";
            book.Price = 99.99;
            //Provider def

            Assert.IsTrue(sqlConn.SaveChanges(book));
            //reads the data from database
            var testdata = sqlConn.GetDataFromStore<E_Book2>();
            Assert.IsNotNull(testdata);
            var testobj = testdata.First();           
            Assert.IsTrue(((E_Book2)testobj).Title == "Titel");
            Assert.IsTrue(book.Title == ((E_Book2)testobj).Title);
            Assert.IsTrue(((E_Book2)testobj).Isbn11 == "111-111-111");
            Assert.IsTrue(book.Isbn11 == ((E_Book2)testobj).Isbn11);

            Assert.IsTrue(((E_Book2)testobj).Price == 99.99);
            Assert.IsTrue(book.Price == ((E_Book2)testobj).Price);
        }

        [TestMethod]
        public void SQLDatabaseWriteAndReadAndUpdateTestAttributes()
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            sqlConn.Connect = StaticConfig.databaseConnection;
            resetDatabase(false, true);

            //Class init
            E_Book2 book = new E_Book2();
            book.Title = "Titel";
            //book. = "1";
            book.Isbn11 = "111-111-111";
            book.Price = 99.99;
            //Provider def

            Assert.IsTrue(sqlConn.SaveChanges(book));
            //reads the data from database
            var testdata = sqlConn.GetDataFromStore<E_Book2>();
            Assert.IsNotNull(testdata);
            var testobj = testdata.First();
            Assert.IsTrue(((E_Book2)testobj).Title == "Titel");
            Assert.IsTrue(book.Title == ((E_Book2)testobj).Title);
            Assert.IsTrue(((E_Book2)testobj).Isbn11 == "111-111-111");
            Assert.IsTrue(book.Isbn11 == ((E_Book2)testobj).Isbn11);


            book.Title = "Titel_update";
            //book. = "1";
            book.Isbn11 = "222-222-222";
            book.Price = 11.91;

            //Provider def

            Assert.IsTrue(sqlConn.SaveChanges(book));
            //reads the data from database
            var testdata2 = sqlConn.GetDataFromStore<E_Book2>();
            Assert.IsNotNull(testdata2);
            var testobj2 = testdata2.First();
            Assert.IsTrue(((E_Book2)testobj2).Title == "Titel_update");
            Assert.IsTrue(book.Title == ((E_Book2)testobj2).Title);
            Assert.IsTrue(((E_Book2)testobj2).Isbn11 == "222-222-222");
            Assert.IsTrue(book.Isbn11 == ((E_Book2)testobj2).Isbn11);
            Assert.IsTrue(((E_Book2)testobj2).Price == 11.91);
            Assert.IsTrue(book.Price == ((E_Book2)testobj2).Price);

        }

        [TestMethod]
        public void SQLDatabaseReferenceReadUpdateTest()
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;
            //Provider def
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            sqlConn.Connect = StaticConfig.databaseConnection;
            resetDatabase(false, true);

            //Class init
            E_Author2 auth = new E_Author2();
            auth.Firstname = "Firstname";
            auth.Lastname = "lastname";

            E_Author2 authCopy = new E_Author2();
            authCopy.Firstname = "Firstname";
            authCopy.Lastname = "lastname";

            
            E_Author2 testauth = new E_Author2();
            testauth.Firstname = "Firstname2";
            testauth.Lastname = "lastname2";

            E_Author2 authCopy2 = new E_Author2();
            authCopy2.Firstname = "Firstname2";
            authCopy2.Lastname = "lastname2";
            //testauth.Created = DateTime.Now.ToString();

            E_Book2 book = new E_Book2();
            book.Title = "Titel";
            //book. = "1";
            book.Isbn11 = "111-111-111";
            book.Price = 99.99;

            E_Book2 testbook = new E_Book2();
            testbook.Title = "Titel";
            //testbook.Gelesen = "1";
            testbook.Isbn11 = "111-111-111";
            testbook.Price = 99.99;

            ////save Classes 
            Assert.IsTrue(sqlConn.SaveChanges(auth));
            Assert.IsTrue(sqlConn.SaveChanges(testauth));

            List<E_Author2> auths = new List<E_Author2>();
            auths.Add(auth);
            book.Authors = (IEnumerable<IObjectRelationModel>)auths;


            Assert.IsTrue(sqlConn.SaveChanges(book));

            //reads the data from database
            var testdata = sqlConn.GetDataFromStore<E_Author2>();
            Assert.IsNotNull(testdata);
            var testobj = testdata.First();

            var testdataB = sqlConn.GetDataFromStore<E_Book2>();
            Assert.IsNotNull(testdataB);
            var testobjB = testdataB.Last();

            Assert.IsTrue(authCopy.Firstname == ((E_Author2)testobj).Firstname);
            Assert.IsTrue(testbook.Title == ((E_Book2)testobjB).Title);
            Assert.IsTrue(((E_Book2)testobjB).Authors.Count() == 1);
            Assert.IsTrue(((E_Author2)((E_Book2)testobjB).Authors.First()).Firstname == authCopy.Firstname);

            Assert.IsTrue(authCopy.Firstname == ((E_Author2)testobj).Firstname);
            Assert.IsTrue(testbook.Title == ((E_Book2)testobjB).Title);
            Assert.IsTrue(((E_Author2)testobj).Books.Count() == 1);
            Assert.IsTrue(((E_Book2)((E_Author2)testobj).Books.First()).Title == testbook.Title);

            auths = new List<E_Author2>();
            auths.Add(testauth);
            book.Authors = (IEnumerable<IObjectRelationModel>)auths;
            Assert.IsTrue(sqlConn.SaveChanges(book));

            var testdata2 = sqlConn.GetDataFromStore<E_Author2>();
            Assert.IsNotNull(testdata2);
            var testobj2 = testdata2.Last();

            var testdataB2 = sqlConn.GetDataFromStore<E_Book2>();
            Assert.IsNotNull(testdataB2);
            var testobjB2 = testdataB2.Last();

            Assert.IsTrue(authCopy2.Firstname == ((E_Author2)testobj2).Firstname);
            Assert.IsTrue(testbook.Title == ((E_Book2)testobjB2).Title);
            Assert.IsTrue(((E_Book2)testobjB2).Authors.Count() == 1);
            Assert.IsTrue(((E_Author2)((E_Book2)testobjB2).Authors.First()).Firstname == authCopy2.Firstname);

            Assert.IsTrue(authCopy2.Firstname == ((E_Author2)testobj2).Firstname);
            Assert.IsTrue(testbook.Title == ((E_Book2)testobjB2).Title);
            Assert.IsTrue(((E_Author2)testobj2).Books.Count() == 1);
            Assert.IsTrue(((E_Book2)((E_Author2)testobj2).Books.First()).Title == testbook.Title);


        }


        /// <summary>
        /// This test writes an object to a databasefile and reads a list, including the written object.
        /// </summary>
        [TestMethod]
        public void SQLDatabaseReferenceReadTest()
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;
            //Provider def
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            sqlConn.Connect = StaticConfig.databaseConnection;
            resetDatabase(false, true);

            //Class init
            E_Author2 auth = new E_Author2();
            auth.Firstname = "Firstname";
            auth.Lastname = "lastname";
            //auth.Created = DateTime.Now.ToString();

            E_Author2 testauth = new E_Author2();
            testauth.Firstname = "Firstname";
            testauth.Lastname = "lastname";
            //testauth.Created = DateTime.Now.ToString();

            E_Book2 book = new E_Book2();
            book.Title = "Titel";
            //book. = "1";
            book.Isbn11 = "111-111-111";
            book.Price = 99.99;

            E_Book2 testbook = new E_Book2();
            testbook.Title = "Titel";
            //testbook.Gelesen = "1";
            testbook.Isbn11 = "111-111-111";
            testbook.Price = 99.99;

            ////save Classes 
            Assert.IsTrue(sqlConn.SaveChanges(auth));
            Assert.IsTrue(sqlConn.SaveChanges(book));

            List<E_Author2> auths = new List<E_Author2>();
            auths.Add(auth);
            book.Authors = (IEnumerable<IObjectRelationModel>)auths;
            book.Isbn11 = "333-333-333";

            Assert.IsTrue(sqlConn.SaveChanges(book));    
            
            //reads the data from database
            var testdata = sqlConn.GetDataFromStore<E_Author2>();
            Assert.IsNotNull(testdata);
            var testobj = testdata.First();

            var testdataB = sqlConn.GetDataFromStore<E_Book2>();
            Assert.IsNotNull(testdataB);
            var testobjB = testdataB.Last();

            Assert.IsTrue(testauth.Firstname == ((E_Author2)testobj).Firstname);
            Assert.IsTrue(testbook.Title == ((E_Book2)testobjB).Title);
            Assert.IsTrue(((E_Book2)testobjB).Authors.Count() > 0);

            Assert.IsTrue(testauth.Firstname == ((E_Author2)testobj).Firstname);
            Assert.IsTrue(testbook.Title == ((E_Book2)testobjB).Title);
            Assert.IsTrue(((E_Author2)testobj).Books.Count() > 0);

        }

        /// <summary>
        /// This test writes many objects to a database and reads a list, including the written object.
        /// </summary>
        [TestMethod]
        public void SQLDatabaseMassDataWithDynamicallyLoadedClasses()
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;

            ClassBuilder cb = new ClassBuilder();

            //Provider def
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            resetDatabase(false, false);
            sqlConn.Connect = StaticConfig.databaseConnection;


            DataDefinitionClass ddBig = new DataDefinitionClass();
            ddBig.createObjects();
            
            DataLoader dl = new DataLoader();
            Type B = null;

            foreach (IObjectDefinition itm in ddBig.Objects)
            {
                cb.BuildClass(itm);

                //The ObjectProvider loads the new generated Class into the testapplication and can now be used in it.
                ObjectProvider.Instance = new ObjectProvider();
                ObjectProvider.Instance.DoImport();
                ObjectProvider.Instance.DoReleaseExport("E_" + itm.OrmName);

                IObjectRelationModel newObj = ((IObjectRelationModel)ObjectProvider.Instance.ProvidedObjects.Where(o => o.GetType().Name == "E_" + itm.OrmName).FirstOrDefault().Instantiate());
                Type Y = newObj.GetType();
                if (itm.OrmName == "Book") { B = Y; }
                Type Z = dl.GetType();
                MethodInfo method = Z.GetMethod("loadData");
                MethodInfo genericMethod = method.MakeGenericMethod(Y);

                Object[] objArr = new Object[2];
                objArr[0] = Path.Combine(StaticConfig.pathDataDefinition, "data" + itm.OrmName + ".xml");
                objArr[1] = newObj;
                genericMethod.Invoke(dl, objArr);

                E_Author2 auth = new E_Author2();
            }

            String sqlCommand = "select count(ID) from BOOK";
            int i;
            sqlConn.runCommand(sqlCommand, "", out i);

            Assert.IsTrue(i > 400);
        }


        /// <summary>
        /// This test writes many objects to a database and reads a list, including the written object.
        /// </summary>
        [TestMethod]
        public void SQLDatabaseMassDataWithDynamicallyLoadedClassesAndReadV1()
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;
            ClassBuilder cb = new ClassBuilder();

            //Provider def
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            sqlConn.Connect = StaticConfig.databaseConnection;
            resetDatabase(true, false);

            DataDefinitionClass ddBig = new DataDefinitionClass();

            ddBig.createObjects();
            //bool test = sqlConn.runCommand(ddBig.BackEndScript);
            DataLoader dl = new DataLoader();

            List<Type> types = new List<Type>();

            foreach (IObjectDefinition itm in ddBig.Objects)
            {
                cb.BuildClass(itm);

                //The ObjectProvider loads the new generated Class into the testapplication and can now be used in it.
                ObjectProvider.Instance = new ObjectProvider();
                ObjectProvider.Instance.DoImport();
                ObjectProvider.Instance.DoReleaseExport("E_" + itm.OrmName);

                IObjectRelationModel newObj = ((IObjectRelationModel)ObjectProvider.Instance.ProvidedObjects.Where(o => o.GetType().Name == "E_" + itm.OrmName).FirstOrDefault().Instantiate());
                Type Y = newObj.GetType();
                types.Add(Y);

                Type Z = dl.GetType();
                MethodInfo method = Z.GetMethod("loadData");
                MethodInfo genericMethod = method.MakeGenericMethod(Y);

                //if (itm.OrmName != "Book")
                {
                    Object[] objArr = new Object[2];
                    objArr[0] = Path.Combine(StaticConfig.pathDataDefinition, "data" + itm.OrmName + ".xml");
                    objArr[1] = newObj;
                    genericMethod.Invoke(dl, objArr);
                }
            }

            String sqlCommand = "select count(ID) from BOOK";
            int i;
            sqlConn.runCommand(sqlCommand, "", out i);

            Assert.IsTrue(i > 400);

            Type K = sqlConn.GetType();

            foreach (Type T in types)
            {
                MethodInfo method2 = K.GetMethod("GetDataFromStore", new Type[] { });
                MethodInfo genericMethod2 = method2.MakeGenericMethod(T);

                var testt = genericMethod2.Invoke(sqlConn, null);
                Assert.IsNotNull(testt);
                Assert.IsTrue(((IEnumerable<IObjectRelationModel>)testt).Count() > 0);

            }

        }


        /// <summary>
        /// This test writes many objects to a database and reads a list, including the written object.
        /// </summary>
        [TestMethod]
        public void SQLDatabaseMassDataWithDynamicallyLoadedClassesAndReadV2()
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;
            ClassBuilder cb = new ClassBuilder();

            //Provider def
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            sqlConn.Connect = StaticConfig.databaseConnection;
            resetDatabase(true, false);

            DataDefinitionClass ddBig = new DataDefinitionClass();

            ddBig.createObjects();
            //bool test = sqlConn.runCommand(ddBig.BackEndScript);
            DataLoader dl = new DataLoader();

            List<Type> types = new List<Type>();

            foreach (IObjectDefinition itm in ddBig.Objects)
            {
                cb.BuildClass(itm);
            }
            ObjectProvider.Instance = new ObjectProvider();
            ObjectProvider.Instance.DoImport();
            //The ObjectProvider loads the new generated Class into the testapplication and can now be used in it.
            
            //ObjectProvider.Instance.DoReleaseExport("E_" + itm.OrmName);
            foreach (IObjectDefinition itm in ddBig.Objects)
            {
                IObjectRelationModel newObj = ((IObjectRelationModel)ObjectProvider.Instance.ProvidedObjects.Where(o => o.GetType().Name == "E_" + itm.OrmName).FirstOrDefault().Instantiate());
                Type Y = newObj.GetType();
                types.Add(Y);

                Type Z = dl.GetType();
                MethodInfo method = Z.GetMethod("loadData");
                MethodInfo genericMethod = method.MakeGenericMethod(Y);

                //if (itm.OrmName != "Book")
                {
                    Object[] objArr = new Object[2];
                    objArr[0] = Path.Combine(StaticConfig.pathDataDefinition, "data" + itm.OrmName + ".xml");
                    objArr[1] = newObj;
                    genericMethod.Invoke(dl, objArr);
                }
            }

            String sqlCommand = "select count(ID) from BOOK";
            int i;
            sqlConn.runCommand(sqlCommand, "", out i);

            Assert.IsTrue(i > 400);

            Type K = sqlConn.GetType();

            foreach (Type T in types)
            {
                MethodInfo method2 = K.GetMethod("GetDataFromStore", new Type[] { });
                MethodInfo genericMethod2 = method2.MakeGenericMethod(T);
                
                var testt = genericMethod2.Invoke(sqlConn, null);
                
                Assert.IsNotNull(testt);
                Assert.IsTrue(((IEnumerable<IObjectRelationModel>)testt).Count() > 0);

            }

        }

        /// <summary>
        /// This test writes an object to a databasefile and reads a list, including the written object.
        /// </summary>
        [TestMethod]
        public void SQLDatabaseCreateAndModifyTest_Simple()
        {
            StaticConfig.Database = e_DataStoreProvider.MSSQLServer;
            //String path = Path.Combine(StaticCofig.pathDataDefinition, "DataDefinition.xml");
            SQLDatabaseProvider sqlConn = new SQLDatabaseProvider();
            sqlConn.Connect = StaticConfig.databaseConnection;
            resetDatabase(true, true);

            string xmlPath = Path.Combine(StaticConfig.pathDataDefinition, "DataDefinitionModify.xml");
            string xmlPathOriginal = Path.Combine(StaticConfig.pathDataDefinition, "DataDefinitionModify_original.xml");

            File.Copy(xmlPathOriginal, xmlPath, true);
            StaticConfig.pathDataDefinitionFile = Path.Combine(StaticConfig.pathDataDefinition, "DataDefinitionModify.xml");

            sqlConn.Connect = StaticConfig.databaseConnection;
            sqlConn.createDatabase();
            
            DataDefinitionClass ddBig = new DataDefinitionClass();
            ddBig.createObjects();
            ClassBuilder cb = new ClassBuilder();

            Type t = null;
            foreach(IObjectDefinition itm in ddBig.Objects)
            {
                if(itm.OrmName == "TestClass")
                {
                    cb.BuildClass(itm);

                    ObjectProvider.Instance = new ObjectProvider();
                    ObjectProvider.Instance.DoImport();
                    ObjectProvider.Instance.DoReleaseExport("E_" + itm.OrmName);
                    IObjectRelationModel newObj = ((IObjectRelationModel)ObjectProvider.Instance.ProvidedObjects.Where(o => o.GetType().Name == "E_" + itm.OrmName).FirstOrDefault().Instantiate());
                    t = newObj.GetType();
                }
            }

            String sqlCommand = "select count(ID) from Testclass where ISBN13 is null";
            int i;
            sqlConn.runCommand(sqlCommand, "", out i);
            //if 0 then database column exists
            Assert.IsTrue(i == 0);

            IObjectRelationModel on = (IObjectRelationModel)Activator.CreateInstance(t);
            foreach (String s in on.GetPropertyNames())
            {
                on.SetValue(s, "Test_" + s);
            }
            sqlConn.SaveChanges(on);

            String sqlCommand2 = "select count(ID) from Testclass where ISBN13 is not null";
            int i2;
            sqlConn.runCommand(sqlCommand2, "", out i2);
            
            Assert.IsTrue(i2 > 0);
            


            //Class init
            E_TestClass2 testclass = new E_TestClass2();
            testclass.Testproperty = "Test";
            //Provider def

            Assert.IsTrue(sqlConn.SaveChanges(testclass));
            //reads the data from database
            var testdata = sqlConn.GetDataFromStore<E_TestClass2>();
            Assert.IsNotNull(testdata);
            var testobj = testdata.First();
            Assert.IsTrue(testclass.Testproperty == ((E_TestClass2)testobj).Testproperty);

        }

    }
}
